# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	# message = []
	# message.append("<br>The value of the Creation To Filter is : " + filters.get("creation_to"))
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	# return ["单据号:Link/SRM Sample Request:150", "优先级:50", "项目名称:150","物料名称:100", "规格型号:200", "元件数量:50",
	# 		"内部状态:80", "采购员:50", "需求日期:150", "供应商:150", "回样日期_采购:150", 
	# 		"实际回样日期:150", "申请人:50", "用料公司名称:150", "申请日期:150", "邮件发送:50", "上传文件:50","实际回样数量:50"
	# 		]
	columns = [
		{
			"label": _("单据号"),
			"fieldtype": "Link",
			"fieldname": "name",
			"options": "SRM Sample Request",
			"width": 150,
		},
		{
			"label": _("优先级"),
			"fieldtype": "Data",
			"fieldname": "priority",
			"width": 70,
		},
		{
			"label": _("项目名称"),
			"fieldtype": "Data",
			"fieldname": "project_name",
			"width": 150,
		},
		{
			"label": _("物料名称"),
			"fieldtype": "Data",
			"fieldname": "sample_category_purchase",
			"width": 100,
		},
		{
			"label": _("规格型号"),
			"fieldtype": "Data",
			"fieldname": "sample_model",
			"width": 280,
		},
		{
			"label": _("元件数量"),
			"fieldtype": "Data",
			"fieldname": "element_qty_2",
			"width": 80,
		},
		{
			"label": _("内部状态"),
			"fieldtype": "Data",
			"fieldname": "workflow_state",
			"width": 80,
		},
		{
			"label": _("采购员"),
			"fieldtype": "Data",
			"fieldname": "purchaser_name",
			"width": 70,
		},
		{
			"label": _("需求日期"),
			"fieldtype": "Date",
			"fieldname": "requirement_date",
			"width": 100,
		},
		{
			"label": _("供应商"),
			"fieldtype": "Data",
			"fieldname": "curren_supplier",
			"width": 150,
		},
		{
			"label": _("回样日期_采购"),
			"fieldtype": "Date",
			"fieldname": "answer_date_from_purchase",
			"width": 120,
		},
		{
			"label": _("实际回样日期"),
			"fieldtype": "Date",
			"fieldname": "real_receive_date",
			"width": 120,
		},
		{
			"label": _("用料公司名称"),
			"fieldtype": "Data",
			"fieldname": "material_company_name",
			"width": 100,
		},
		{
			"label": _("申请人"),
			"fieldtype": "Data",
			"fieldname": "applicant_name",
			"width": 70,
		},
		{
			"label": _("申请日期"),
			"fieldtype": "Date",
			"fieldname": "creation",
			"width": 120,
		},
		{
			"label": _("邮件发送"),
			"fieldtype": "Data",
			"fieldname": "admit_closed",
			"width": 80,
		},
		{
			"label": _("上传文件"),
			"fieldtype": "Data",
			"fieldname": "sample_file",
			"width": 80,
		},
		{
			"label": _("实际回样数量"),
			"fieldtype": "Data",
			"fieldname": "real_receive_qty",
			"width": 120,
		},
		{
			"label": _("Confirm Return Date"),
			"fieldtype": "Date",
			"fieldname": "confirm_return_date",
			"width": 120,
		},
		{
			"label": _("回复超期"),
			"fieldtype": "Data",
			"fieldname": "days",
			"width": 100,
		},
		{
			"label": _("实交超期"),
			"fieldtype": "Data",
			"fieldname": "days2",
			"width": 100,
		},
	]

	return columns


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	priority, workflow_state, creation_from, creation_to = filters.get('priority'), filters.get('workflow_state'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if priority:
		conditions += f" AND s1.priority = '{priority}' "
	if workflow_state:
		conditions += f" AND s1.workflow_state = '{workflow_state}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""select s1.name, s1.priority, s1.project_name, IFNULL(s1.sample_category_purchase,'') as sample_category_purchase, 
			s1.sample_model, s1.element_qty_2, 
			case s1.workflow_state when 'Save' then '保存'
					when 'Hand In' then '提交'
					when 'Be Received' then '待收料'
					when 'Rejected' then '拒绝'
					when 'Finish' then '完成' 
					when 'Cancelled' then '取消'
					when 'Return From Purchase' then '采购打回'  end as workflow_state, 
			IFNULL(s1.purchaser_name,'') as purchaser_name,
			s1.requirement_date, IFNULL(s1.curren_supplier, '') as curren_supplier, IFNULL(s1.answer_date_from_purchase, '') as answer_date_from_purchase,
			IFNULL(s1.real_receive_date, '') as real_receive_date, s1.applicant_name, IFNULL(s1.material_company_name,'') as material_company_name,
			DATE_FORMAT(s1.creation, '%Y-%m-%d') as creation,
			DATE_FORMAT(s1.confirm_return_date,'%Y-%m-%d') as confirm_return_date,
			case datediff(IFNULL(s1.confirm_return_date, DATE(NOW())), s1.approval_date) when 0 then '正常'
				when 1 then '正常'
				when 2 then '正常'
				when 3 then '正常'
				else ( datediff(IFNULL(s1.confirm_return_date, DATE(NOW())), s1.approval_date) - 3 ) end as days,
			if(datediff(s1.real_receive_date, IFNULL(s1.answer_date_from_purchase, DATE(NOW()))) > 0, datediff(s1.real_receive_date, IFNULL(s1.answer_date_from_purchase, DATE(NOW()))), '正常') as days2,
			case s1.admit_closed when 1 then '已发送'
					when 0 then '未发送' end as admit_closed, 
			concat('<a href=', s1.sample_file, '>', '下载', '</a>') as sample_file, IFNULL(s1.real_receive_qty,'') as real_receive_qty
			from `tabSRM Sample Request` s1 
			WHERE s1.creation >= '{creation_from}' and s1.creation <= '{creation_to}' {conditions}
			ORDER BY s1.creation DESC;
		""", as_dict=True
	)

	return dt